package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Miss Chen
 * @version 1.0
 * @date 2024年08月09日8:57
 */
public class Test15_练习 {
    public static void main(String[] args) throws DataAccessException {
        // ClassBean classBean=new ClassBean("wbs24081","java");
        // List<StudentBean> students=new ArrayList<>();
        // StudentBean stu1=new StudentBean("1001","zhangsan1",21);
        // StudentBean stu2=new StudentBean("1002","zhangsan2",20);
        // StudentBean stu3=new StudentBean("1003","zhangsan3",23);
        // StudentBean stu4=new StudentBean("1004","zhangsan4",24);
        // students.add(stu1);
        // students.add(stu2);
        // students.add(stu3);
        // students.add(stu4);
        // insertRetrunPrimaryKey(classBean,students);

        // ClassBean classBean=selectClassById(1);
        // System.out.println(classBean);

        List<StudentBean> students=selectAllStus();
        System.out.println(students);
    }

    /**
     * 保存班级以及该班级中的所有学生信息
     * @param classBean
     * @param students
     * @throws DataAccessException
     */
    public static void  insertRetrunPrimaryKey(ClassBean classBean, List<StudentBean> students) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Integer id=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append(" (no,major) ")
                    .append(" values ")
                    .append(" (?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1,classBean.getNo());
            ps.setString(2,classBean.getMajor());
            ps.executeUpdate();
            rs=ps.getGeneratedKeys();
            while(rs.next()){
                id=rs.getInt(1);
            }
            classBean.setId(id);

           sql=new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append(" (no,name,age,class_id) ")
                    .append(" values ")
                    .append(" (?,?,?,?) ")
                    .toString();
            ps=conn.prepareStatement(sql);
            for(StudentBean student:students){
                student.setClassBean(classBean);
                ps.setString(1,student.getNo());
                ps.setString(2,student.getName());
                ps.setInt(3,student.getAge());
                ps.setInt(4,student.getClassBean().getId());
                ps.executeUpdate();
            }

        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }

    /**
     * 根据班级id查询班级以及该班级中的所有学生信息
     * @param id
     * @return
     * @throws DataAccessException
     */
    public static ClassBean selectClassById(Integer id) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        ClassBean classBean=null;
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id,s.no,s.name,s.age ")
                    .append(" from t_class c ")
                    .append(" left join t_student s ")
                    .append(" on c.id=s.class_id ")
                    .append(" where c.id=? ")
                    .toString();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            while(rs.next()){
                System.out.println("===============");
                if(classBean==null){
                    //一对多映射
                    //当第一次进来的时候，映射部门对象
                    //后面再次进来，不再对部门进行映射
                    // 当classBean为null值的说明尚未映射过
                    // 如果对classBean进行映射，则classBean的值不为null
                    classBean=new ClassBean();
                    classBean.setId(rs.getInt("c.id"));
                    classBean.setNo(rs.getString("c.no"));
                    classBean.setMajor(rs.getString("c.major"));
                }

                //对于学生信息，每遍历一次，映射一个员工对象
                // 每次进来都需要对员工进行映射
                StudentBean student=new StudentBean();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));
                classBean.addStudent(student);
            }
            return classBean;
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException("数据库访问异常");
        } finally {
            JDBCUtil.close(conn,ps,rs);
        }
    }



    public static List<StudentBean> selectAllStus(){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<StudentBean> students=new ArrayList<>();
        try {
            conn=JDBCUtil.getConnection();
            String sql=new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id,c.no,c.major  ")
                    .append(" from t_student s ")
                    .append(" left join t_class c ")
                    .append(" on s.class_id=c.id ")
                    .toString();
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                //此时映射涉及到2个对象
                StudentBean student=new StudentBean();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));

                ClassBean classBean=new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("c.major"));

                student.setClassBean(classBean);
                students.add(student);

            }

        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        return students;
    }
}
